
package com.feib.soeasy.dao.jdbc;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;
import javax.sql.DataSource;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.support.JdbcDaoSupport;
import org.springframework.stereotype.Repository;

import com.feib.soeasy.dao.Soez5004Dao;
import com.feib.soeasy.model.WriteOffPayment;


/**
 * @title (#)Soez5004JdbcDao.java<br>
 * @description <br>
 * @author Jimmy Liu<br>
 * @version 1.0.0 2010/11/25
 * @copyright Far Eastern International Bank Copyright (c) 2010<br>
 * @2011/3/14 create by Jimmy Liu<br>
 */
@Repository("soez5004Dao")
public class Soez5004JdbcDao extends JdbcDaoSupport implements Soez5004Dao {
    protected Logger logger = LoggerFactory.getLogger(this.getClass());

    @Resource(name = "dataSource")
    public void setMyDataSource(DataSource dataSource) {
        super.setDataSource(dataSource); 
    }
    
    /**
     * 超商統計資料
     * @param groupPk 帳單業者
     * @param channel 通路, D:7-11, E:OK, F:全家, G:萊爾富, NULL: 4家超商
     * @param qryDateType 資料期間基礎：1: 實際入帳日  2:繳款交易日。
     * @param qryDateFrom 資料期間起
     * @param qryDateTo 資料期間迄
     * @return 
     */
    public List<Map<String, Object>> getStatisticsForStore(Long groupPk, String channel, Integer qryDateType, Date qryDateFrom, Date qryDateTo)
    {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        
        /* 第二個版本 */
        sql.append("SELECT ");
        sql.append("CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME "); //繳款通路, 代收類別
        sql.append(", COUNT(PAYMENT_PK) AS RECORD_COUNT "); //代收筆數小計
        //sql.append(", SUM(AMOUNT) AS AMOUNT "); //代收金額小計
        //sql.append(", SUM(COMMISSION_AMOUNT) AS COMMISSION_AMOUNT "); //代收手續費小計
        sql.append(", SUM(BANK_AMOUNT) AS BANK_AMOUNT "); // 帳務處理費小計
        //sql.append(", SUM(AMOUNT -COMMISSION_AMOUNT - BANK_AMOUNT ) AS REAL_AMOUNT "); //實際入帳金額小計
        sql.append("FROM ");
        sql.append("( ");
        sql.append("SELECT TMP.* ");
        sql.append(", C.COMMISSION_CODE_PK ");
        sql.append(", C.COMMISSION_CODE_NAME ");
        sql.append(", C.COMMISSION_TYPE ");
        //sql.append(", ISNULL(C.COMMISSION_AMOUNT, 0 ) AS COMMISSION_AMOUNT ");
        sql.append(", ISNULL(G.CHAINSTORE_AMOUNT, 0 ) AS BANK_AMOUNT ").append("\r\n"); //銀行帳務處理費, 20120121 jimmy
        //sql.append(", ISNULL(C.BANK_AMOUNT, 0 ) AS BANK_AMOUNT ");
        //sql.append(", ISNULL(C.PAYER_AMOUNT, 0 ) AS PAYER_AMOUNT ");
        //sql.append(", ISNULL(GROUP_AMOUNT, 0 ) AS GROUP_AMOUNT ");//代收手續費
        sql.append("FROM ");
        sql.append("( ");
        sql.append("select P.GROUP_PK, W.WRITE_OFF_PAYMENT_PK ");
        sql.append(", P.PAYMENT_PK ");
        sql.append(", U.FORMAT_DEF_PK ");
        sql.append(", W. AMOUNT ");
        sql.append(", W.WRITE_OFF_DATE ");
        sql.append(", W.CREATED_DATE ");
        sql.append(", P.BARCODE_STORES_1 ");
        sql.append(", P.TOTAL_AMOUNT ");
        sql.append(", P.TOTAL_LOANABLE_AMOUNT ");
        sql.append(", W.CHANNEL ");
        sql.append(", CT.CODE_NAME AS CHANNEL_NAME");
        sql.append(", CASE  W.AMOUNT WHEN P.TOTAL_AMOUNT THEN SUBSTRING( P.BARCODE_STORES_1, 7, 9) ELSE  SUBSTRING( P.BARCODE_STORES_1_LOAN, 7, 9) END AS COMMISSION_CODE ");
        sql.append(" from WRITE_OFF_PAYMENT w INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ");
        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK ");
        sql.append(" INNER JOIN CODE_TABLE CT ON CT.CODE_KIND='CHANEL' AND CT.CODE_VALUE = W.CHANNEL ");
        sql.append(" where P.PAYMENT_STATUS = 5 "); //已繳款
        sql.append(" AND P.GROUP_PK = ? "); // 帳單業者
        args.add(groupPk);
        
        if (null != channel)
        {
            sql.append(" AND W.CHANNEL = ? "); // 通路
            args.add(channel);
        }
        else
        {
            sql.append(" AND W.CHANNEL in( ?, ?, ?, ?) "); // 通路
            args.add(WriteOffPayment.CHANNEL_7_11);
            args.add(WriteOffPayment.CHANNEL_FAMILY_MART);
            args.add(WriteOffPayment.CHANNEL_HILIFE);
            args.add(WriteOffPayment.CHANNEL_OK_MART);
        }
        
        // 資料期間 1: 銷帳日期  2:實際繳款日
        if (new Integer(1).equals(qryDateType))
            sql.append(" AND W.WRITE_OFF_DATE between ? and ? "); // 銷帳日期
        else
            sql.append(" AND W.TX_DATE between ? and ? "); // 實際繳款日

        args.add(qryDateFrom);
        args.add(qryDateTo);
        
        sql.append(") TMP ");
        sql.append(" ");
        sql.append("INNER JOIN FORMAT_DEF_COMMISSION F ON F.FORMAT_DEF_PK = TMP.FORMAT_DEF_PK ");
        sql.append("INNER JOIN COMMISSION_CODE_TABLE C  ON C.COMMISSION_CODE_PK = F.COMMISSION_CODE_PK AND C.COMMISSION_CODE = TMP.COMMISSION_CODE ");
        sql.append("INNER JOIN APP_GROUP G ON G.GROUP_PK = TMP.GROUP_PK ").append("\r\n");
        sql.append(") D ");
        sql.append("GROUP BY CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME ");
        sql.append("ORDER BY CHANNEL, COMMISSION_CODE_PK ");
        
        logger.debug("5004getStatisticsForStore sql={};  args={}", sql, args);
        
        
        logger.debug("5004rs= {} ", this.getJdbcTemplate().queryForList(sql.toString(), args.toArray()).toString());
        
        
        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
        
    }
    
    /**
     * 非超商統計資料
     * @param groupPk
     * @param channel
     * @param qryType
     * @param qryDateFrom
     * @param qryDateTo
     * @return
     */
    public List<Map<String, Object>> getStatisticsForNotStore(Long groupPk, String channel, Integer qryType, Date qryDateFrom, Date qryDateTo)
    {
        StringBuffer sql = new StringBuffer();
        List<Object> args = new ArrayList<Object>();
        sql.append("SELECT ");
        sql.append("CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME "); //繳款通路, 代收類別
        sql.append(", COUNT(PAYMENT_PK) AS RECORD_COUNT ");//代收筆數小計
        //sql.append(", SUM(AMOUNT) AS AMOUNT "); //代收金額小計
        //sql.append(", 0 AS COMMISSION_AMOUNT "); //代收手續費小計
        sql.append(", SUM(BANK_AMOUNT) AS BANK_AMOUNT "); // 帳務處理費小計
        //sql.append(", SUM(AMOUNT -COMMISSION_AMOUNT - BANK_AMOUNT ) AS REAL_AMOUNT "); //實際入帳金額小計
        sql.append("FROM ");
        sql.append("( ");
        sql.append("SELECT TMP.* ");
        sql.append(", C.COMMISSION_CODE_PK ");
        sql.append(", C.COMMISSION_CODE_NAME ");
        sql.append(", C.COMMISSION_TYPE ");
        /*sql.append(", ISNULL(C.COMMISSION_AMOUNT, 0 ) AS COMMISSION_AMOUNT ");
        sql.append(", ISNULL(C.BANK_AMOUNT, 0 ) AS BANK_AMOUNT ");
        sql.append(", ISNULL(C.PAYER_AMOUNT, 0 ) AS PAYER_AMOUNT ");
        sql.append(", ISNULL(GROUP_AMOUNT, 0 ) AS GROUP_AMOUNT ");
        */

        //帳務處理費 20120121 jimmy
        sql.append(",CASE TMP.CHANNEL WHEN '1' THEN ISNULL(G.ATM_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN '2' THEN ISNULL(G.ATM_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN '3' THEN ISNULL(G.ATM_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN '4' THEN ISNULL(G.ATM_AMOUNT, 0 ) ").append("\r\n"); 
        sql.append(" WHEN '0' THEN ISNULL(G.BANK_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN '8' THEN ISNULL(G.REMITTANCE_AMOUNT, 0 ) ").append("\r\n");
        sql.append(" WHEN 'H' THEN ISNULL(G.POST_AMOUNT, 0 ) ").append("\r\n");
        
        /* 20120828 自行繳款之「帳務處理費」計算   */
        sql.append(" WHEN 'I' THEN ISNULL(G.OTHER_AMOUNT, 0 )  ").append("\r\n");
        sql.append(" WHEN 'Z' THEN ISNULL(G.SELF_AMOUNT, 0 ) ").append("\r\n"); 
        sql.append(" ELSE  0 ").append("\r\n"); 
        
        sql.append(" END AS BANK_AMOUNT ").append("\r\n");//帳務處理費
        
        sql.append("FROM ");
        sql.append("( ");
        sql.append("select P.GROUP_PK, W.WRITE_OFF_PAYMENT_PK ");
        sql.append(", P.PAYMENT_PK ");
        sql.append(", U.FORMAT_DEF_PK ");
        sql.append(", W. AMOUNT ");
        sql.append(", W.WRITE_OFF_DATE ");
        sql.append(", W.CREATED_DATE ");
        sql.append(", P.BARCODE_STORES_1 ");
        sql.append(", P.TOTAL_AMOUNT ");
        sql.append(", P.TOTAL_LOANABLE_AMOUNT ");
        sql.append(", W.CHANNEL ");
        sql.append(", CT.CODE_NAME AS CHANNEL_NAME");
        sql.append(", SUBSTRING( P.BARCODE_STORES_1, 7, 9) AS COMMISSION_CODE ");
        sql.append(" from WRITE_OFF_PAYMENT w INNER JOIN PAYMENT P ON W.WRITE_OFF_PAYMENT_PK = P.WRITE_OFF_PAYMENT_PK ");
        sql.append(" INNER JOIN UPLOAD_FILE U ON U.UPLOAD_FILE_PK = P.UPLOAD_FILE_PK ");
        sql.append(" INNER JOIN CODE_TABLE CT ON CT.CODE_KIND='CHANEL' AND CT.CODE_VALUE = W.CHANNEL ");
        sql.append(" where P.PAYMENT_STATUS = 5 "); //已繳款
        sql.append(" AND P.GROUP_PK = ? "); // 帳單業者
        args.add(groupPk);
        
        if (null != channel)
        {
            sql.append(" AND W.CHANNEL = ? "); // 通路
            args.add(channel);
        }
        else
        {
            sql.append(" AND W.CHANNEL NOT IN ( ?, ?, ?, ?) "); // 通路
            args.add(WriteOffPayment.CHANNEL_7_11);
            args.add(WriteOffPayment.CHANNEL_FAMILY_MART);
            args.add(WriteOffPayment.CHANNEL_HILIFE);
            args.add(WriteOffPayment.CHANNEL_OK_MART);
        }
        
        // 資料期間
        if (new Integer(1).equals(qryType))
            sql.append(" AND W.CREATED_DATE between ? and ? "); // 實際入帳日
        else
            sql.append(" AND W.WRITE_OFF_DATE between ? and ? "); // 繳款交易日

        args.add(qryDateFrom);
        args.add(qryDateTo);
        
        sql.append(") TMP ");
        sql.append(" ");
        sql.append("INNER JOIN FORMAT_DEF_COMMISSION F ON F.FORMAT_DEF_PK = TMP.FORMAT_DEF_PK ");
        sql.append("INNER JOIN COMMISSION_CODE_TABLE C  ON C.COMMISSION_CODE_PK = F.COMMISSION_CODE_PK AND C.COMMISSION_CODE = TMP.COMMISSION_CODE ");
        sql.append("INNER JOIN APP_GROUP G ON G.GROUP_PK = TMP.GROUP_PK ").append("\r\n");
        sql.append(") D ");
        sql.append("GROUP BY CHANNEL, CHANNEL_NAME, COMMISSION_CODE_PK, COMMISSION_CODE_NAME ");
        sql.append("ORDER BY CHANNEL, COMMISSION_CODE_PK ");
        
        logger.debug("5004getStatisticsForNotStore sql={};  args={}", sql, args);
        
        
        logger.debug("5004rs= {} ", this.getJdbcTemplate().queryForList(sql.toString(), args.toArray()).toString());
        
        

        return this.getJdbcTemplate().queryForList(sql.toString(), args.toArray());
        
    }
    
}
